/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cis;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Scanner;

//This class is used to access the CIS database
class CisDatabase {
    
    //Get a user details
    static ResultSet getAccount(String username) throws Exception {
        Connection con = getConnection();
        String sql = "select Username, Password, Role from Account where Username=?";
        PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ps.setString(1,username);
        ResultSet result = null;
        result = ps.executeQuery();
        return result;
    }
    
    //update a user password
    static void updateAccount(String username, String newPassword) throws Exception {
        Connection conn = getConnection();
        String sql = "update Account set Password = ? where Username = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(2, username);
        ps.setString(1, newPassword);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }
    
    //Get all Product details
    static ResultSet getAllProducts() throws Exception {
        Connection conn = getConnection();
        String sql = "select ProductID, " +
                "ProductName, " +
                "Comment " +
                "from Product";
        PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet result = null;
        result = ps.executeQuery();
        return result;
    }
    
    //add a product to database and ResultSet
    static void addProduct(ResultSet products, String productName, String comment) throws Exception {
        products.moveToInsertRow();
        products.updateString(2, productName);
        products.updateString(3, comment);
        products.insertRow();
    }
    
    //update a product in database and ResultSet
    static void updateProduct(ResultSet products, int row, String comment) throws Exception {
        products.beforeFirst();
        int counter = 0;
        
        while (products.next()) {
            if (products.rowDeleted())
                continue;
            else {
                counter++;
                if (counter == row) {
                    products.updateString(3, comment);
                    products.updateRow();
                    break;
                }
            }
        }
    }
    
    //delete a product from database and ResultSet
    static void deleteProduct(ResultSet products, int row) throws Exception {
        products.beforeFirst();
        int counter = 0;
        
        while (products.next()) {
            if (products.rowDeleted())
                continue;
            else {
                counter++;
                if (counter == row) {
                    products.deleteRow();
                    break;
                }
            }
        }
    }
    
    //get the product name from the ResultSet using product id
    static String getProductNameById(ResultSet products, Integer id) throws Exception {
        products.beforeFirst();
                
        while (products.next()) {
            if (products.rowDeleted())
                continue;
            else
                if (products.getObject(1).equals(id))
                    return products.getString(2);
        }
        return "";
    }
    
    //get the product id from the ResultSet using product name
    static Integer getProductIdByName(ResultSet products, String name) throws Exception {
        products.beforeFirst();

        while (products.next()) {
            if (products.rowDeleted())
                continue;
            else
                if (products.getObject(2).equals(name))
                    return products.getInt(1);
        }
        return new Integer(-1);
    }
    
    //Get all Campaign details
    static ResultSet getAllCampaigns() throws Exception {
        Connection conn = getConnection();
        String sql = "select CampaignID, " +
                "ProductID, " +
                "Type, " +
                "Period, " +
                "ActualBudget, " +
                "ActualSales, " +
                "EstimatedBudget, " +
                "EstimatedSales, " +
                "convert(int, (convert(float, ActualBudget)/EstimatedBudget)*100) BudgetVariance, " +
                "convert(int, (convert(float, ActualSales)/EstimatedSales)*100) SalesVariance " +
                "from Campaign";
        PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet result = null;
        result = ps.executeQuery();
        return result;
    }
    
    //add a campaign to database and ResultSet
    static void addCampaign(ResultSet campaigns, 
            int productId, 
            String type, 
            int period, 
            int actualBudget, 
            int actualSales, 
            int estimatedBudget, 
            int estimatedSales) throws Exception {
        
        campaigns.moveToInsertRow();
        campaigns.updateInt(2, productId);
        campaigns.updateString(3, type);
        campaigns.updateInt(4, period);
        campaigns.updateInt(5, actualBudget);
        campaigns.updateInt(6, actualSales);
        campaigns.updateInt(7, estimatedBudget);
        campaigns.updateInt(8, estimatedSales);
        campaigns.insertRow();
    }
    
    //update a campaign in database and ResultSet
    static void updateCampaign(ResultSet campaigns, 
            int row,
            int period, 
            int actualBudget, 
            int actualSales, 
            int estimatedBudget, 
            int estimatedSales) throws Exception {
        
        campaigns.beforeFirst();
        int counter = 0;
        while (campaigns.next()) {
            if (campaigns.rowDeleted())
                continue;
            else {
                counter++;
                if (counter == row) {
                    campaigns.updateInt(4, period);
                    campaigns.updateInt(5, actualBudget);
                    campaigns.updateInt(6, actualSales);
                    campaigns.updateInt(7, estimatedBudget);
                    campaigns.updateInt(8, estimatedSales);
                    campaigns.updateRow();
                    break;
                }
            }
        }
    }
    
    //delete a campaign from database and ResultSet
    static void deleteCampaign(ResultSet campaigns, 
            int row) throws Exception {
        campaigns.beforeFirst();
        int counter = 0;
        
        while (campaigns.next()) {
            if (campaigns.rowDeleted())
                continue;
            else {
                counter++;
                if (counter == row) {
                    campaigns.deleteRow();
                    break;
                }
            }
        }
    }
    
    //get the number of rows of the ResultSet
    static int getResulSetRowCount(ResultSet resultSet) throws Exception {
        resultSet.beforeFirst();
        int counter = 0;
        
        while (resultSet.next()) {
            if (resultSet.rowDeleted())
                continue;
            else
                counter++;
        }
        
        return counter;
    }
    
    //get the number of columns of the ResultSet
    static int getResulSetColumnCount(ResultSet resultSet) throws Exception {
        return resultSet.getMetaData().getColumnCount();
    }
    
    //get the value of the ResultSet at the position (row, column)
    static Object getResultSetValue(ResultSet resultSet, int row, int column) throws Exception {
        resultSet.beforeFirst();
        int counter = 0;
        
        while (resultSet.next()) {
            if (resultSet.rowDeleted())
                continue;
            else {
                counter++;
                if (counter == row)
                    return resultSet.getObject(column);
            }
        }
        
        return null;
    }
    
    //get a Connection object
    private static Connection getConnection() throws Exception
    {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        
        //get the lines from the the text file
        ArrayList<String> strings = new ArrayList<String>();
        Scanner input = new Scanner(
                new FileInputStream("Database/Configuration.txt"));
        while (input.hasNext()) {
            strings.add(input.nextLine().replaceAll("\\s", ""));
        }
        
        //get the values from the lines
        String server = "";
        String  port = "";
        String  username = "";
        String  password = "";
        for (String str : strings) {
            String[] pair = str.split("=");
            
            if (pair.length == 2) 
                if (pair[0].equalsIgnoreCase("server"))
                    server = pair[1];
                else if (pair[0].equalsIgnoreCase("port"))
                    port = pair[1];
                else if (pair[0].equalsIgnoreCase("username"))
                    username = pair[1];
                else if (pair[0].equalsIgnoreCase("password"))
                    password = pair[1];
        }
        input.close();

        //create the connection
        String url = "jdbc:sqlserver://" + 
                server + ":" + 
                port + ";database=CampaignInformationSystem;" ;
        Connection conn = DriverManager.getConnection(url, username, password);

        return conn;
    }
}
